﻿using System;
using System.ComponentModel;
using System.Data;
using System.Web.Services;
using Shuba.Model;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.IO;
using System.DirectoryServices;
using Shuba.Framework;
using System.IO.Compression;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Web;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for wsb2y15
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class wsb2y15 : System.Web.Services.WebService
    {
        string AppCode = ConfigurationManager.AppSettings["app.code.spada"];

        byte[] TransformIntoBytes(DataSet ds)
        {
            byte[] data;
            MemoryStream memStream = new MemoryStream();
            GZipStream zipStream = new GZipStream(memStream, CompressionMode.Compress);
            ds.WriteXml(zipStream, XmlWriteMode.WriteSchema);
            zipStream.Close();
            data = memStream.ToArray();
            memStream.Close();
            return data;
        }

        DataSet TransformIntoDataSet(byte[] value)
        {
            using (MemoryStream memStream = new MemoryStream(value))
            {
                using (GZipStream unzipStream = new GZipStream(memStream, CompressionMode.Decompress))
                {
                    DataSet objDataSet = new DataSet();
                    objDataSet.ReadXml(unzipStream, XmlReadMode.ReadSchema);
                    unzipStream.Close();
                    memStream.Close();
                    return objDataSet;
                }
            }
        }

        bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
        }

        void CreateErrorLog(string text, bool isnewline)
        {
            try
            {
                string appPath = Server.MapPath("~/AppLog");
                if (appPath.Substring(appPath.Length - 1, 1) == "\\")
                {
                    appPath = appPath.Substring(0, appPath.Length - 1);
                }

                if (File.Exists(String.Format("{0}\\Log.txt", appPath)))
                {
                    using (StreamWriter file = new StreamWriter(String.Format("{0}\\Log.txt", appPath), true))
                    {
                        if (!isnewline) file.WriteLine();
                        file.WriteLine(String.Format("{0}", text));
                    }
                }
                else
                {
                    File.WriteAllText(String.Format("{0}\\Log.txt", appPath), text);
                }
            }
            catch (Exception) { }
        }

        [WebMethod]
        public byte[] GetFundingRefferalUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetFundingRefferalUM");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetFundingRefferalSO(string nip, string pass, string unit, string macAddress)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetFundingRefferalSO");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, macAddress);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public int UFundingReffealAssignedTo(DateTime AssignedDate, string REFFERAL_ID, string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_FUNDING_REFFERAL SET IS_ASSIGNED = '1', ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE REFFERAL_ID=@REFFERAL_ID");

                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public bool UFundingRefferalIsDownloaded(List<string> REFFERAL_ID, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd;
                for (int i = 0; i < REFFERAL_ID.Count; i++)
                {
                    cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_FUNDING_REFFERAL SET IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE WHERE REFFERAL_ID = @REFFERAL_ID");

                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LoginUser);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, DateTime.Now);
                    db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID[i]);
                    db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            return false;
        }

        [WebMethod]
        public string UploadProspectFundingRefferal(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_FUNDING_REFFERAL 
                                SET IS_DOWNLOADED = '0', IS_ASSIGNED = '0' 
                                WHERE REFFERAL_ID=@REFFERAL_ID;
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_FUNDING_REFFERAL
                                (
                                    REFFERAL_ID
                                    ,FULL_NAME
                                    ,PRODUCT_CODE
                                    ,OFFICE_PHONE
                                    ,RENCANA_SETORAN_AWAL
                                    ,ADDRESS_BUSINESS
                                    ,UNIT_CODE_SOURCE
                                    ,UNIT_CODE_DEST
                                    ,AO_CODE_ORIG
                                    ,RES_100
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @REFFERAL_ID
                                    ,@FULL_NAME
                                    ,@PRODUCT_CODE
                                    ,@OFFICE_PHONE
                                    ,@RENCANA_SETORAN_AWAL
                                    ,LEFT(@ADDRESS_BUSINESS,255)
                                    ,@UNIT_CODE_SOURCE
                                    ,@UNIT_CODE_DEST
                                    ,@AO_CODE_ORIG
                                    ,@RES_100
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                            ");
                        db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, Convert.ToString(dr["REFFERAL_ID"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, Convert.ToString(dr["PRODUCT_CODE"]));
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                        db.AddInParameter(cmd, "@RENCANA_SETORAN_AWAL", DbType.Double, Convert.ToString(dr["RENCANA_SETORAN_AWAL"]));
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                        db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, Convert.ToString(dr["UNIT_CODE_SOURCE"]));
                        db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, Convert.ToString(dr["UNIT_CODE_DEST"]));
                        db.AddInParameter(cmd, "@AO_CODE_ORIG", DbType.String, Convert.ToString(dr["AO_CODE_ORIG"]));
                        db.AddInParameter(cmd, "@RES_100", DbType.String, Convert.ToString(dr["RES_100"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToString(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToString(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) res++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectFundingRefferal : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public byte[] GetHubSpoke(string nip, string pass, string BRANCH_ID, string NIP, string ID, DateTime start, DateTime end)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetHubSpokeSPADA");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@ID", DbType.String, ID);
                db.AddInParameter(cmd, "@DATE_START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@DATE_END", DbType.DateTime, end);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public int GetCountFundingRefferal(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCountFundingRefferal");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public byte[] GetMaintenanceVisit(string table, string unit, string AOTELLER, DateTime start, DateTime end, string LoginUser, string LoginPass)
        {
            DataSet ds = new DataSet();
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("WS_GET_MAINTENANCE_VISIT");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@ID", DbType.String, table);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AOTELLER", DbType.String, AOTELLER);
                db.AddInParameter(cmd, "@DATE_START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@DATE_END", DbType.DateTime, end);

                ds = db.ExecuteDataSet(cmd);
            }
            return TransformIntoBytes(ds);
        }
    }
}
